﻿using Dapper;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;

namespace WMSManage.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]//需要鉴权
    public class DashboardController : ControllerBase
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        /// <summary>
        /// 获取今日和昨日出入库统计数据
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetDashboardStats()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                sqlConnection.Open();
                var sql = @"
                -- 今日入库统计
                SELECT COUNT(*) as todayInOrderCount FROM [dbo].[in_instock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(GETDATE() AS DATE);
                SELECT ISNULL(SUM(in_instockOK_num), 0) as todayInNum FROM [dbo].[in_instock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(GETDATE() AS DATE);
                SELECT ISNULL(SUM(in_total), 0) as todayInMoney FROM [dbo].[in_instock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(GETDATE() AS DATE);
                
                -- 今日出库统计
                SELECT COUNT(*) as todayOutOrderCount FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(GETDATE() AS DATE);
                SELECT ISNULL(SUM(out_outstockOK_num), 0) as todayOutNum FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(GETDATE() AS DATE);
                SELECT ISNULL(SUM(out_total), 0) as todayOutMoney FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(GETDATE() AS DATE);
                
                -- 昨日入库统计
                SELECT COUNT(*) as yesterdayInOrderCount FROM [dbo].[in_instock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
                SELECT ISNULL(SUM(in_instockOK_num), 0) as yesterdayInNum FROM [dbo].[in_instock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
                SELECT ISNULL(SUM(in_total), 0) as yesterdayInMoney FROM [dbo].[in_instock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
                
                -- 昨日出库统计
                SELECT COUNT(*) as yesterdayOutOrderCount FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
                SELECT ISNULL(SUM(out_outstockOK_num), 0) as yesterdayOutNum FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
                SELECT ISNULL(SUM(out_total), 0) as yesterdayOutMoney FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND CAST(create_time AS DATE) = CAST(DATEADD(day, -1, GETDATE()) AS DATE);
            "
                ;

                using var multi = await sqlConnection.QueryMultipleAsync(sql);

                var result = new
                {
                    todayInOrderCount = await multi.ReadSingleAsync<int>(),
                    todayInNum = await multi.ReadSingleAsync<int>(),
                    todayInMoney = await multi.ReadSingleAsync<decimal>(),
                    todayOutOrderCount = await multi.ReadSingleAsync<int>(),
                    todayOutNum = await multi.ReadSingleAsync<int>(),
                    todayOutMoney = await multi.ReadSingleAsync<decimal>(),
                    yesterdayInOrderCount = await multi.ReadSingleAsync<int>(),
                    yesterdayInNum = await multi.ReadSingleAsync<int>(),
                    yesterdayInMoney = await multi.ReadSingleAsync<decimal>(),
                    yesterdayOutOrderCount = await multi.ReadSingleAsync<int>(),
                    yesterdayOutNum = await multi.ReadSingleAsync<int>(),
                    yesterdayOutMoney = await multi.ReadSingleAsync<decimal>()
                };

                return Ok(result);
            }
        }


        /// <summary>
        /// 获取待审核和待出入库
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetTodoStats()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                            -- 待审核统计
                            SELECT COUNT(*) as pendingInstock FROM [dbo].[in_instock_h] WHERE is_delete=0 AND in_status=2;
                            SELECT COUNT(*) as pendingOutstock FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND out_status=2;
                            SELECT COUNT(*) as pendingAllot FROM [dbo].[tr_allot_h] WHERE is_delete=0 AND al_status=2;
                            SELECT COUNT(*) as pendingPurchase FROM [dbo].[ord_purchase_h] WHERE is_delete=0 AND pur_status=2;
                            SELECT COUNT(*) as pendingSale FROM [dbo].[ord_sale_h] WHERE is_delete=0 AND sale_status=2;
                            SELECT COUNT(*) as pendingPurchaseReturn FROM [dbo].[ord_purchase_return_h] WHERE is_delete=0 AND purt_status=2;
                            SELECT COUNT(*) as pendingSaleReturn FROM [dbo].[ord_sale_return_h] WHERE is_delete=0 AND salet_status=2;
            
                            -- 待执行统计
                            SELECT COUNT(*) as waitingInstock FROM [dbo].[in_instock_h] WHERE is_delete=0 AND (in_status=4 OR in_status=5);
                            SELECT COUNT(*) as waitingOutstock FROM [dbo].[out_outstock_h] WHERE is_delete=0 AND (out_status=7 OR out_status=8);
                        ";

                using var multi = await connection.QueryMultipleAsync(sql);

                var result = new
                {
                    pendingInstock = await multi.ReadSingleAsync<int>(),
                    pendingOutstock = await multi.ReadSingleAsync<int>(),
                    pendingAllot = await multi.ReadSingleAsync<int>(),
                    pendingPurchase = await multi.ReadSingleAsync<int>(),
                    pendingSale = await multi.ReadSingleAsync<int>(),
                    pendingPurchaseReturn = await multi.ReadSingleAsync<int>(),
                    pendingSaleReturn = await multi.ReadSingleAsync<int>(),
                    waitingInstock = await multi.ReadSingleAsync<int>(),
                    waitingOutstock = await multi.ReadSingleAsync<int>()
                };

                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取待办事项失败", error = ex.Message });
            }
        }



        /// <summary>
        /// 获取预警信息
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetWarningStats()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                    -- 库存上限预警
                    SELECT COUNT(*) as highLimitCount FROM [dbo].[sys_stock_warning] WHERE warning_type='高于上限';
            
                    -- 库存下限预警
                    SELECT COUNT(*) as lowLimitCount FROM [dbo].[sys_stock_warning] WHERE warning_type='低于下限';
            
                    -- 到期货品
                    SELECT COUNT(DISTINCT s.proc_id) as expiredCount 
                    FROM [dbo].[bs_stock] s
                    INNER JOIN [dbo].[bs_product] p ON s.proc_id = p.proc_id
                    INNER JOIN [dbo].[in_instock_d] ind ON s.proc_id = ind.proc_id
                    WHERE p.is_delete = 0
                    AND p.is_datamanagement = 1
                    AND s.stk_hand_num > 0
                    AND ind.proc_date IS NOT NULL
                    AND (
                        (p.proc_expirationdate LIKE '%天' AND DATEDIFF(day, ind.proc_date, GETDATE()) > CAST(REPLACE(p.proc_expirationdate, '天', '') AS INT))
                        OR 
                        (p.proc_expirationdate LIKE '%个月' AND DATEDIFF(month, ind.proc_date, GETDATE()) > CAST(REPLACE(p.proc_expirationdate, '个月', '') AS INT))
                        OR 
                        (p.proc_expirationdate LIKE '%年' AND DATEDIFF(year, ind.proc_date, GETDATE()) > CAST(REPLACE(p.proc_expirationdate, '年', '') AS INT))
                    );
            
                    -- 临期货品
                    SELECT COUNT(DISTINCT s.proc_id) as nearExpiredCount 
                    FROM [dbo].[bs_stock] s
                    INNER JOIN [dbo].[bs_product] p ON s.proc_id = p.proc_id
                    INNER JOIN [dbo].[in_instock_d] ind ON s.proc_id = ind.proc_id
                    WHERE p.is_delete = 0
                    AND p.is_datamanagement = 1
                    AND s.stk_hand_num > 0
                    AND ind.proc_date IS NOT NULL
                    AND (
                        -- 按天计算的临期货品（剩余天数 <= 预警天数）
                        (p.proc_expirationdate LIKE '%天' 
                         AND (CAST(REPLACE(p.proc_expirationdate, '天', '') AS INT) - DATEDIFF(day, ind.proc_date, GETDATE())) <= p.proc_Warningdays
                         AND DATEDIFF(day, ind.proc_date, GETDATE()) <= CAST(REPLACE(p.proc_expirationdate, '天', '') AS INT))
                        OR 
                        -- 按月计算的临期货品
                        (p.proc_expirationdate LIKE '%个月' 
                         AND DATEDIFF(day, ind.proc_date, DATEADD(month, CAST(REPLACE(p.proc_expirationdate, '个月', '') AS INT), ind.proc_date)) <= p.proc_Warningdays
                         AND DATEDIFF(month, ind.proc_date, GETDATE()) <= CAST(REPLACE(p.proc_expirationdate, '个月', '') AS INT))
                        OR
                        -- 按年计算的临期货品
                        (p.proc_expirationdate LIKE '%年' 
                         AND DATEDIFF(day, ind.proc_date, DATEADD(year, CAST(REPLACE(p.proc_expirationdate, '年', '') AS INT), ind.proc_date)) <= p.proc_Warningdays
                         AND DATEDIFF(year, ind.proc_date, GETDATE()) <= CAST(REPLACE(p.proc_expirationdate, '年', '') AS INT))
				     );
                ";

                using var multi = await connection.QueryMultipleAsync(sql);

                var result = new
                {
                    highLimitCount = await multi.ReadSingleAsync<int>(),
                    lowLimitCount = await multi.ReadSingleAsync<int>(),
                    expiredCount = await multi.ReadSingleAsync<int>(),
                    nearExpiredCount = await multi.ReadSingleAsync<int>()
                };

                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取预警信息失败", error = ex.Message });
            }
        }



        /// <summary>
        /// 查询库存占比数量和金额
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetWarehouseStockStats()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                    SELECT 
                        w.wh_name as warehouseName,
                        w.wh_id as warehouseId,
                        ISNULL(SUM(s.stk_hand_num), 0) as stockCount,
                        ISNULL(SUM(s.stk_total), 0) as stockAmount
                    FROM [dbo].[bs_warehouse] w
                    LEFT JOIN [dbo].[bs_stock] s ON w.wh_id = s.wh_id
                    WHERE w.is_delete = 0
                    GROUP BY w.wh_id, w.wh_name
                    ORDER BY w.wh_id
                ";

                var warehouseStats = await connection.QueryAsync(sql);

                // 计算总数量和总金额
                var totalCount = warehouseStats.Sum(x => (int)x.stockCount);
                var totalAmount = warehouseStats.Sum(x => (decimal)x.stockAmount);

                var result = new
                {
                    warehouseStats = warehouseStats.Select(x => new
                    {
                        warehouseName = x.warehouseName,
                        warehouseId = x.warehouseId,
                        stockCount = (int)x.stockCount,
                        stockAmount = (decimal)x.stockAmount,
                        countPercentage = totalCount > 0 ? Math.Round((decimal)(int)x.stockCount / totalCount * 100, 2) : 0,
                        amountPercentage = totalAmount > 0 ? Math.Round((decimal)x.stockAmount / totalAmount * 100, 2) : 0
                    }),
                    totalCount = totalCount,
                    totalAmount = totalAmount
                };

                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取仓库库存统计失败", error = ex.Message });
            }
        }




        /// <summary>
        /// 入库统计
        /// </summary>
        /// <param name="timeType"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetInstockStats(string timeType = "7")
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                DateTime startDate;
                switch (timeType)
                {
                    case "7":
                        startDate = DateTime.Now.AddDays(-7);
                        break;
                    case "30":
                        startDate = DateTime.Now.AddDays(-30);
                        break;
                    case "365":
                        startDate = DateTime.Now.AddDays(-365);
                        break;
                    default:
                        startDate = DateTime.Now.AddDays(-7);
                        break;
                }

                // 获取统计数据
                var sql = @"
                    SELECT 
                        CONVERT(date, create_time) as date,
                        ISNULL(SUM(in_num), 0) as orderCount,
                        ISNULL(SUM(in_total), 0) as totalAmount
                    FROM [dbo].[in_instock_h] 
                    WHERE create_time >= @startDate 
                        AND is_delete = 0
                    GROUP BY CONVERT(date, create_time)
                    ORDER BY date
                ";

                var stats = await connection.QueryAsync(sql, new { startDate });

                // 生成完整的日期范围
                var result = new List<object>();
                var currentDate = startDate.Date;
                var endDate = DateTime.Now.Date;

                while (currentDate <= endDate)
                {
                    var dayStats = stats.FirstOrDefault(x => ((DateTime)x.date).Date == currentDate);

                    result.Add(new
                    {
                        date = currentDate.ToString("yyyy-MM-dd"),
                        orderCount = dayStats?.orderCount ?? 0,
                        totalAmount = dayStats?.totalAmount ?? 0
                    });

                    currentDate = currentDate.AddDays(1);
                }

                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取入库统计失败", error = ex.Message });
            }
        }



        /// <summary>
        /// 出库统计
        /// </summary>
        /// <param name="timeType"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetOutstockStats(string timeType = "7")
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                DateTime startDate;
                switch (timeType)
                {
                    case "7":
                        startDate = DateTime.Now.AddDays(-7);
                        break;
                    case "30":
                        startDate = DateTime.Now.AddDays(-30);
                        break;
                    case "365":
                        startDate = DateTime.Now.AddDays(-365);
                        break;
                    default:
                        startDate = DateTime.Now.AddDays(-7);
                        break;
                }

                // 获取统计数据
                var sql = @"
                    SELECT 
                        CONVERT(date, create_time) as date,
                        ISNULL(SUM(out_num), 0) as orderCount,
                        ISNULL(SUM(out_total), 0) as totalAmount
                    FROM [dbo].[out_outstock_h] 
                    WHERE create_time >= @startDate 
                        AND is_delete = 0
                    GROUP BY CONVERT(date, create_time)
                    ORDER BY date
                ";

                var stats = await connection.QueryAsync(sql, new { startDate });

                // 生成完整的日期范围
                var result = new List<object>();
                var currentDate = startDate.Date;
                var endDate = DateTime.Now.Date;

                while (currentDate <= endDate)
                {
                    var dayStats = stats.FirstOrDefault(x => ((DateTime)x.date).Date == currentDate);

                    result.Add(new
                    {
                        date = currentDate.ToString("yyyy-MM-dd"),
                        orderCount = dayStats?.orderCount ?? 0,
                        totalAmount = dayStats?.totalAmount ?? 0
                    });

                    currentDate = currentDate.AddDays(1);
                }

                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取出库统计失败", error = ex.Message });
            }
        }



        /// <summary>
        /// 入库排名
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetInstockRanking()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                     SELECT TOP 10
                        ROW_NUMBER() OVER (ORDER BY SUM(d.ind_num) DESC) as rank,
                        p.proc_name as name,
                        SUM(d.ind_num) as quantity,
                        SUM(d.ind_total) as amount,
                        COUNT(DISTINCT h.in_id) as times
                    FROM [dbo].[in_instock_h] h
                    INNER JOIN [dbo].[in_instock_d] d ON h.in_id = d.in_id
                    INNER JOIN [dbo].[bs_product] p ON d.proc_id = p.proc_id
                    WHERE h.is_delete = 0 
                    GROUP BY p.proc_name, p.proc_id
                    ORDER BY SUM(d.ind_num) DESC
                ";

                var ranking = await connection.QueryAsync(sql);
                return Ok(ranking);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取入库排名失败", error = ex.Message });
            }
        }




        /// <summary>
        /// 出库排名
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetOutstockRanking()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                    SELECT TOP 10
                        ROW_NUMBER() OVER (ORDER BY SUM(d.outd_num) DESC) as rank,
                        p.proc_name as name,
                        SUM(d.outd_num) as quantity,
                        SUM(d.outd_total) as amount,
                        COUNT(DISTINCT h.out_id) as times
                    FROM [dbo].[out_outstock_h] h
                    INNER JOIN [dbo].[out_outstock_d] d ON h.out_id = d.out_id
                    INNER JOIN [dbo].[bs_product] p ON d.proc_id = p.proc_id
                    WHERE h.is_delete = 0
                    GROUP BY p.proc_name, p.proc_id
                    ORDER BY SUM(d.outd_num) DESC
                ";

                var ranking = await connection.QueryAsync(sql);
                return Ok(ranking);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取出库排名失败", error = ex.Message });
            }
        }



        /// <summary>
        /// 系统信息
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetSystemMessages()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                    SELECT
                        info_text,
                        info_time
                    FROM [dbo].[sys_info]
                    ORDER BY info_time DESC
                ";

                var messages = await connection.QueryAsync(sql);
                return Ok(messages);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取系统消息失败", error = ex.Message });
            }
        }



        /// <summary>
        /// 获取信息数量
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetMessageCounts()
        {
            try
            {
                using var connection = new SqlConnection(con);
                await connection.OpenAsync();

                var sql = @"
                          SELECT info_type,COUNT(*) as count
                            FROM [dbo].[sys_info]
                            WHERE  info_status = 2
                            GROUP BY info_type
                 ";

                var counts = await connection.QueryAsync(sql);

                var result = new
                {
                    total = counts.Sum(x => (int)x.count),
                    details = counts.ToDictionary(x => (string)x.info_type, x => (int)x.count)
                };

                return Ok(result);
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { message = "获取消息数量失败", error = ex.Message });
            }
        }
    }
}
